package org.gdstash.db;

import org.gdstash.file.DDSLoader;
import org.gdstash.file.GDParseException;
import org.gdstash.util.GDMsgFormatter;
import org.gdstash.util.GDMsgLogger;

import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class DBSkillConnector {
    public static final String TABLE_NAME = "GD_SKILL_CONNECTOR";
    public static final String FIELD_ID = "SKILL_ID";
    private static final int ROW_SKILL_ID = 1;
    private static final int ROW_INDEX = 2;
    private static final int ROW_CONNECTION_OFF_ID = 3;
    private static final int ROW_CONNECTION_OFF = 4;
    private static final int ROW_CONNECTION_ON_ID = 5;
    private static final int ROW_CONNECTION_ON = 6;
    private String skillID;
    private int index;
    private String connectionOffID;
    private String connectionOnID;
    private byte[] connectionOff;
    private byte[] connectionOn;
    private BufferedImage imageOff;
    private BufferedImage imageOn;

    public DBSkillConnector() {
    }

    public DBSkillConnector(final int index, final String connectionOffID, final String connectionOnID) {
        this.index = index;
        this.connectionOffID = connectionOffID;
        this.connectionOnID = connectionOnID;
    }

    public int getIndex() {
        return this.index;
    }

    public BufferedImage getConnectionOff() {
        return this.imageOff;
    }

    public BufferedImage getConnectionOn() {
        return this.imageOn;
    }

    public void setConnectionOffID(final String connectionOffID) {
        this.connectionOffID = connectionOffID;
    }

    public void setConnectionOnID(final String connectionOnID) {
        this.connectionOnID = connectionOnID;
    }

    public static void createTable(final Connection conn) throws SQLException {
        final String dropTable = "DROP TABLE GD_SKILL_CONNECTOR";
        final String createTable = "CREATE TABLE GD_SKILL_CONNECTOR (SKILL_ID  VARCHAR(256) NOT NULL, INDEX             INTEGER, CONNECTION_OFF_ID VARCHAR(256), CONNECTION_OFF    BLOB(128K), CONNECTION_ON_ID  VARCHAR(256), CONNECTION_ON     BLOB(128K), PRIMARY KEY (SKILL_ID, INDEX))";
        try (final Statement st = conn.createStatement()) {
            if (GDDBUtil.tableExists(conn, "GD_SKILL_CONNECTOR")) {
                st.execute(dropTable);
            }
            st.execute(createTable);
            st.close();
            conn.commit();
        } catch (SQLException ex) {
            final Object[] args = {"GD_SKILL_CONNECTOR"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_CREATE_TABLE", args);
            GDMsgLogger.addError(msg);
            throw ex;
        }
    }

    public static void delete(final Connection conn, final String skillID) throws SQLException {
        final String deleteEntry = "DELETE FROM GD_SKILL_CONNECTOR WHERE SKILL_ID = ?";
        try (final PreparedStatement ps = conn.prepareStatement(deleteEntry)) {
            ps.setString(1, skillID);
            ps.executeUpdate();
            ps.close();
        } catch (SQLException ex) {
            throw ex;
        }
    }

    public static void insert(final Connection conn, final String skillID, final List<DBSkillConnector> connections) throws SQLException {
        final String insert = "INSERT INTO GD_SKILL_CONNECTOR VALUES (?,?,?,?,?,?)";
        if (connections == null) {
            return;
        }
        if (connections.isEmpty()) {
            return;
        }
        try (final PreparedStatement ps = conn.prepareStatement(insert)) {
            for (final DBSkillConnector connection : connections) {
                ps.setString(1, skillID);
                ps.setInt(2, connection.index);
                ps.setString(3, connection.connectionOffID);
                if (connection.connectionOff == null) {
                    ps.setBlob(4, (Blob) null);
                } else {
                    ps.setBlob(4, new ByteArrayInputStream(connection.connectionOff));
                }
                ps.setString(5, connection.connectionOnID);
                if (connection.connectionOn == null) {
                    ps.setBlob(6, (Blob) null);
                } else {
                    ps.setBlob(6, new ByteArrayInputStream(connection.connectionOn));
                }
                ps.executeUpdate();
                ps.clearParameters();
            }
            ps.close();
        }
    }

    public static List<DBSkillConnector> getBySkillID(final String skillID) {
        if (skillID == null) {
            return null;
        }
        List<DBSkillConnector> list = new LinkedList<DBSkillConnector>();
        final String command = "SELECT * FROM GD_SKILL_CONNECTOR WHERE SKILL_ID = ?ORDER BY INDEX";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.setString(1, skillID);
            try (final ResultSet rs = ps.executeQuery()) {
                list = wrap(rs);
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {skillID, "GD_SKILL_CONNECTOR"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return list;
    }

    private static List<DBSkillConnector> wrap(final ResultSet rs) throws SQLException {
        final LinkedList<DBSkillConnector> list = new LinkedList<DBSkillConnector>();
        Blob blob = null;
        while (rs.next()) {
            final DBSkillConnector connection = new DBSkillConnector();
            connection.skillID = rs.getString(1);
            connection.index = rs.getInt(2);
            connection.connectionOffID = rs.getString(3);
            blob = rs.getBlob(4);
            if (blob == null) {
                connection.connectionOff = null;
            } else {
                connection.connectionOff = blob.getBytes(1L, (int) blob.length());
            }
            if (connection.connectionOff != null) {
                try {
                    connection.imageOff = DDSLoader.getImage(connection.connectionOff);
                } catch (GDParseException ex) {
                    final Object[] args = {connection.skillID};
                    final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_BITMAP_DECODE_FAILED", args);
                    GDMsgLogger.addError(msg);
                    connection.imageOff = null;
                }
            }
            connection.connectionOnID = rs.getString(5);
            blob = rs.getBlob(6);
            if (blob == null) {
                connection.connectionOn = null;
            } else {
                connection.connectionOn = blob.getBytes(1L, (int) blob.length());
            }
            if (connection.connectionOn != null) {
                try {
                    connection.imageOn = DDSLoader.getImage(connection.connectionOn);
                } catch (GDParseException ex) {
                    final Object[] args = {connection.skillID};
                    final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_BITMAP_DECODE_FAILED", args);
                    GDMsgLogger.addError(msg);
                    connection.imageOn = null;
                }
            }
            list.add(connection);
        }
        return list;
    }

    public static List<ImageInfo> getImageInfos(final String id) {
        final List<ImageInfo> list = new LinkedList<ImageInfo>();
        Blob blob = null;
        final String command = "SELECT SKILL_ID, INDEX, CONNECTION_OFF_ID, CONNECTION_OFF, CONNECTION_ON_ID, CONNECTION_ON FROM GD_SKILL_CONNECTOR WHERE ((CONNECTION_OFF_ID LIKE '" + id + "%' AND CONNECTION_OFF IS NULL) OR (CONNECTION_ON_ID LIKE '" + id + "%' AND CONNECTION_ON IS NULL))";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            try (final ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    final ImageInfo info = new ImageInfo();
                    info.skillID = rs.getString(1);
                    info.index = rs.getInt(2);
                    info.connectionOffID = rs.getString(3);
                    blob = rs.getBlob(4);
                    if (blob == null) {
                        info.connectionOff = null;
                    } else {
                        info.connectionOff = blob.getBytes(1L, (int) blob.length());
                    }
                    info.connectionOnID = rs.getString(5);
                    blob = rs.getBlob(6);
                    if (blob == null) {
                        info.connectionOn = null;
                    } else {
                        info.connectionOn = blob.getBytes(1L, (int) blob.length());
                    }
                    list.add(info);
                }
                conn.commit();
            } catch (Exception ex) {
                throw ex;
            }
        } catch (Exception ex2) {
            GDMsgLogger.addError(ex2);
        }
        return list;
    }

    public static void deleteNoImageSkills() {
        final String command = "DELETE FROM GD_SKILL_CONNECTOR WHERE BITMAP_UP IS NULL";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.execute();
            conn.commit();
        } catch (Exception ex) {
            GDMsgLogger.addError(ex);
        }
    }

    public static void updateImageInfo(final List<ImageInfo> list) {
        if (list == null) {
            return;
        }
        final String command = "UPDATE GD_SKILL_CONNECTOR SET CONNECTION_OFF = ?, CONNECTION_ON = ? WHERE SKILL_ID = ? AND INDEX = ?";
        try (final Connection conn = GDDBData.getConnection()) {
            final boolean auto = conn.getAutoCommit();
            conn.setAutoCommit(false);
            for (final ImageInfo info : list) {
                try (final PreparedStatement ps = conn.prepareStatement(command)) {
                    if (info.connectionOff != null || info.connectionOn != null) {
                        if (info.connectionOff == null) {
                            ps.setBlob(1, (Blob) null);
                        } else {
                            ps.setBlob(1, new ByteArrayInputStream(info.connectionOff));
                        }
                        if (info.connectionOn == null) {
                            ps.setBlob(2, (Blob) null);
                        } else {
                            ps.setBlob(2, new ByteArrayInputStream(info.connectionOn));
                        }
                        ps.setString(3, info.skillID);
                        ps.setInt(4, info.index);
                        ps.executeUpdate();
                        ps.close();
                        conn.commit();
                    }
                } catch (SQLException ex) {
                    conn.rollback();
                    final Object[] args = {info.skillID};
                    final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_IN_ITEM_IMAGE_SIZE", args);
                    GDMsgLogger.addWarning(msg);
                    GDMsgLogger.addWarning(ex);
                }
            }
        } catch (SQLException ex2) {
            GDMsgLogger.addError(ex2);
        }
    }

    public static class ImageInfo {
        public String skillID;
        public int index;
        public String connectionOffID;
        public String connectionOnID;
        public byte[] connectionOff;
        public byte[] connectionOn;
    }
}
